package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportMapper extends BaseMapper {

    @Select("SELECT  count(tm.ID) as memberCounts,  " +
            "concat(year(rp.fdate),\"-\",rp.fmonth) as yearAndMonth " +
            "from rpt_date  rp  ,t_member  tm " +
            "where rp.fdate = tm.REGTIME " +
            "and rp.fyear = year(now())-1 " +
            "group by rp.fmonth")
    List<Map> findLastyearMemberCountsByMonth();


    @Select("SELECT s. NAME as name, count(o.setmeal_id) as value " +
            "FROM t_order o INNER JOIN t_setmeal s " +
            "ON o.setmeal_id = s.id " +
            "GROUP BY o.setmeal_id")
    List<Map> getSetmealProportion();

    @Select("select  count(tm.ID) as memberCounts,concat(year(rp.fdate),\"-\",rp.fmonth) as yearAndMonth " +
            "from rpt_date  rp,t_member  tm " +
            "where rp.fdate = tm.REGTIME " +
            "and tm.REGTIME BETWEEN #{begin} AND #{end}" +
            "group by rp.fmonth")
    List<Map> findMemberCountsByMonth(String begin, String end);




    /**
     * description:*运营报表、热门套餐
     */
    @Select("select count(1) from t_member")
    int findTotalMember();

    @Select("select count(1) from t_member where t_member.REGTIME=#{nowDate}")
    int findTodayNewMember(@Param("nowDate") String nowDate);

    @Select("select count(1) from t_order where ORDERDATE=#{nowDate}")
    int findTodayOrderNumber(@Param("nowDate") String nowDate);

    @Select("select count(1) from t_order where ORDERDATE=#{nowDate} and ORDERSTATUS='已到诊'")
    int findTodayVisitsNumber(@Param("nowDate") String nowDate);

    @Select("select count(1) from t_member where REGTIME BETWEEN #{thisWeekMonday} and #{sundayOfThisWeek} ")
    int findThisWeekNewMember(@Param("thisWeekMonday") String thisWeekMonday, @Param("sundayOfThisWeek") String sundayOfThisWeek);

    @Select("select count(1) from t_order where ORDERDATE BETWEEN #{thisWeekMonday} AND #{sundayOfThisWeek}")
    int findThisWeekOrderNumber(@Param("thisWeekMonday") String thisWeekMonday, @Param("sundayOfThisWeek") String sundayOfThisWeek);

    @Select("select count(1) from t_order where ORDERDATE BETWEEN #{thisWeekMonday} AND #{sundayOfThisWeek} and ORDERSTATUS='已到诊'")
    int findThisWeekVisitsNumber(@Param("thisWeekMonday") String thisWeekMonday, @Param("sundayOfThisWeek") String sundayOfThisWeek);

    @Select("select count(1) from t_member where REGTIME BETWEEN #{firstDayMonth} and #{endDayMonth}")
    int findThisMonthNewMember(@Param("firstDayMonth") String firstDayMonth, @Param("endDayMonth") String endDayMonth);

    @Select("select count(1) from t_order where ORDERDATE BETWEEN #{firstDayMonth} AND #{endDayMonth}")
    int findThisMonthOrderNumber(@Param("firstDayMonth") String firstDayMonth, @Param("endDayMonth") String endDayMonth);

    @Select("select count(1) from t_order where ORDERDATE BETWEEN #{firstDayMonth} AND #{endDayMonth} and ORDERSTATUS='已到诊'")
    int findThisMonthVisitsNumber(@Param("firstDayMonth") String firstDayMonth, @Param("endDayMonth") String endDayMonth);

    @Select("SELECT ts.`NAME` AS 'name',count(ts.ID)AS setmeal_count,ts.REMARK AS 'remark' " +
            "FROM t_setmeal ts,t_order tor " +
            "WHERE tor.SETMEAL_ID=ts.ID " +
            "GROUP BY ts.ID " +
            "ORDER BY setmeal_count DESC")
    List<Map> findHotSetmeal();

    @Select("select count(1) from t_order")
    int findSetmealcount();

    /**
     * description:男、女会员数，各个年龄段的个数
     */
    @Select("select SEX AS 'name',count(1) AS value " +
            "from t_member " +
            "where sex='1' " +
            "GROUP BY SEX")
    Map findManCount();

    @Select("select count(1) AS value ,SEX AS 'name' " +
            "from t_member " +
            "where sex='0' " +
            "GROUP BY SEX")
    Map findWomanCount();

    @Select("SELECT BIRTHDAY AS name,count(1) AS value FROM t_member WHERE BIRTHDAY<= #{currentTime} AND BIRTHDAY>= #{zeroToEighteen}")
    Map findOneToEighteen(@Param("currentTime") String currentTime, @Param("zeroToEighteen") String zeroToEighteen);

    @Select("SELECT BIRTHDAY AS name,count(1) AS value FROM t_member WHERE BIRTHDAY> #{startSeventeen} AND BIRTHDAY<= #{endOfThirtyFour}")
    Map findNineteenToThirtyFive(@Param("startSeventeen") String startSeventeen,@Param("endOfThirtyFour") String endOfThirtyFour);

    @Select("SELECT BIRTHDAY AS name,count(1) AS value FROM t_member WHERE BIRTHDAY>=#{startFortyFive} AND BIRTHDAY<=#{endThirtyFive}")
    Map findThirtySixToFortyFive(@Param("startFortyFive") String startFortyFive,@Param("endThirtyFive") String endThirtyFive);

    @Select("SELECT BIRTHDAY AS name,count(1) AS value FROM t_member WHERE BIRTHDAY< #{overFortyFive}")
    Map findGreaterThanFortyFive(@Param("overFortyFive") String overFortyFive);
}
